package com.ruoyi.sqlbuilder.execute.handler;

import com.ruoyi.sqlbuilder.domain.Columns;
import com.ruoyi.sqlbuilder.domain.KeyColumnUsage;
import com.ruoyi.sqlbuilder.domain.Statistics;
import com.ruoyi.sqlbuilder.execute.constants.ConvertConstants;
import com.ruoyi.sqlbuilder.execute.convert.SqlHandler;
import com.ruoyi.sqlbuilder.execute.relation.MysqlToHgColumnType;
import com.ruoyi.sqlbuilder.execute.util.ActSqlUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * HigHgo sql处理类
 *
 * @author Yanzy
 */
public class HigHgoSqlHandler extends SqlHandler {

    /**
     * 创建table sql 头部
     *
     * @param tableName   table名称
     * @param tableSchema 表架构
     * @return sql
     */
    @Override
    public String createTableHead(String tableName, String tableSchema) {
        StringBuilder head = new StringBuilder();
        head.append("-- ----------------------------\r\n");
        head.append(String.format("-- Table structure for %s\r\n", tableName));
        head.append("-- ----------------------------\r\n");
        head.append(String.format("DROP TABLE IF EXISTS public.%s; \r\n", tableName));
        head.append(String.format("CREATE TABLE public.%s ( \r\n", tableName));
        head.append("%s \r\n");
        head.append(");");
        return head.toString();
    }

    /**
     * 创建table sql 身体部分
     *
     * @param columns 字段
     * @return sql
     */
    @Override
    public String createTableBody(List<Columns> columns) {

        List<String> sqlArr = new ArrayList<>();
        // 主键
        List<String> priKeys = new ArrayList<>();
        StringBuffer sql;
        for (Columns column : columns) {
            sql = new StringBuffer();
            // name
            sql.append(StringUtils.SPACE).append("\"").append(column.getColumnName()).append("\"").append(StringUtils.SPACE);
            // varchar(12)
            sql.append(handleColumnType(column)).append(handlePrecision(column)).append(StringUtils.SPACE);
            if (!"auto_increment".equalsIgnoreCase(column.getExtra())) {
                // not null
                String notNullPre = handleNotNullPre(column);
                if (StringUtils.isNotBlank(notNullPre)) {
                    sql.append(notNullPre).append(StringUtils.SPACE);
                }
                // default value
                String defaultValue = handleDefaultValue(column);
                if (StringUtils.isNotBlank(defaultValue)) {
                    sql.append(defaultValue).append(StringUtils.SPACE);
                }
            }
            if ("PRI".equalsIgnoreCase(column.getColumnKey())) {
                priKeys.add("\"" + column.getColumnName() + "\"");
            }
            sqlArr.add(sql.toString());
        }
        if (CollectionUtils.isNotEmpty(priKeys)) {
            sqlArr.add(StringUtils.SPACE + handlePrimaryKey(priKeys));
        }
        return StringUtils.join(sqlArr, ",\r\n");
    }

    @Override
    public String createTableSpecial(List<Columns> columns) {
        // 自增主键拼接语句
        StringBuilder autoPriPrecisionSql = new StringBuilder();
        for (Columns column : columns) {
            if (StringUtils.isNotBlank(autoPriPrecisionSql)) {
                break;
            }
            if ("auto_increment".equalsIgnoreCase(column.getExtra()) && "PRI".equalsIgnoreCase(column.getColumnKey())) {
                autoPriPrecisionSql.append("DROP SEQUENCE IF EXISTS public.");
                // 约定的自增key
                autoPriPrecisionSql.append(column.getTableName()).append("_").append(column.getColumnName()).append("_seq;\r\n");
                autoPriPrecisionSql.append("CREATE SEQUENCE public.");

                autoPriPrecisionSql.append(column.getTableName()).append("_").append(column.getColumnName()).append("_seq");
                autoPriPrecisionSql.append(" INCREMENT 1 MINVALUE 1 MAXVALUE 999999999 START 1024 CACHE 1;");
                autoPriPrecisionSql.append("\r\n");
            }
        }
        return autoPriPrecisionSql.toString();
    }

    /**
     * 处理字段类型组装sql
     *
     * @param columns 字段
     * @return sql
     */
    @Override
    public String handleColumnType(Columns columns) {
        List<String> tinyintToBooleanTable = Arrays.asList("ACT_RE_PROCDEF", "ACT_GE_BYTEARRAY", "ACT_RU_EXECUTION");
        if ("tinyint".equalsIgnoreCase(columns.getDataType()) && tinyintToBooleanTable.contains(columns.getTableName().toLowerCase())) {
            return "boolean";
        }

        if ("tinyint(1)".equalsIgnoreCase(columns.getColumnType())) {
            return "int2";
        }
        if ("auto_increment".equalsIgnoreCase(columns.getExtra())) {
            return "int8 NOT NULL DEFAULT nextval('" + columns.getTableName() + "_" + columns.getColumnName() + "_seq'::regclass)";
            // return "BIGSERIAL";
        }
        if ("longblob".equalsIgnoreCase(columns.getColumnType())) {
            return "bytea";
        }
        return MysqlToHgColumnType.getRelationType(columns.getDataType());
    }

    /**
     * 处理表主键
     *
     * @param primaryKeys 主键集合
     * @return sql
     */
    @Override
    public String handlePrimaryKey(List<String> primaryKeys) {
        StringBuilder sql = new StringBuilder(StringUtils.EMPTY);
        if (CollectionUtils.isNotEmpty(primaryKeys)) {
            sql.append("PRIMARY KEY (").append(StringUtils.join(primaryKeys, ",")).append(")");
        }
        return sql.toString();
    }

    /**
     * 处理行数据 -> sql
     *
     * @param columns   所有列
     * @param values    所有列对应的值
     * @param tableName 表名
     * @return sql
     */
    @Override
    public String handleTableValue(List<Columns> columns, Map<String, Object> values, String tableName) {
        // 处理数据与字段对应关系 转换为insert语句
        StringBuilder sql = new StringBuilder();
        // 组装所有字段
        sql.append(String.format("INSERT INTO public.%s (", tableName));
        List<String> names = columns.stream().map(Columns::getColumnName).collect(Collectors.toList());
        sql.append(StringUtils.join(names, ","))
                .append(") VALUES ( ");
        List<String> columnValues = new ArrayList<>();
        for (Columns column : columns) {
            String columnName = column.getColumnName();
            if (!values.containsKey(columnName)) {
                columnValues.add("NULL");
                continue;
            }
            if (null == values.get(columnName)) {
                columnValues.add("NULL");
                continue;
            }
            // 特殊处理
            if ("BYTES_".equalsIgnoreCase(columnName)) {
                columnValues.add(ActSqlUtil.getBytesValues(ConvertConstants.DB_HIGHGO, values.get("NAME_").toString()));
            } else if (values.get(columnName).equals(true) || values.get(columnName).equals(false)) {
                columnValues.add(String.format("'%s'", values.get(columnName).equals(true) ? 1 : 0));
            } else {
                String v = values.getOrDefault(columnName, "").toString();
                v = v.replaceAll("\\\\", "\\\\\\\\");
                v = v.replaceAll("'", "''");
                columnValues.add(String.format("'%s'", v));
            }
        }
        sql.append(StringUtils.join(columnValues, ","));
        sql.append(" );");
        return sql.toString();
    }

    @Override
    public String handleForeignKey(KeyColumnUsage keyColumnUsage) {
        return super.handleForeignKey(keyColumnUsage);
    }

    @Override
    public String handleIndex(Statistics statistics) {
        String sql;
        if (statistics.getNonUnique() == 0) {
            // 唯一索引
            sql = String.format("CREATE UNIQUE INDEX %s ON public.%s( %s );", statistics.getIndexName(),
                    statistics.getTableName(), statistics.getColumnName());
            ;
        } else {
            sql = String.format("CREATE INDEX %s ON public.%s( %s );", statistics.getIndexName(),
                    statistics.getTableName(), statistics.getColumnName());
        }
        return sql;
    }

    @Override
    public String handleIndexList(List<Statistics> statistics) {
        StringBuilder sql = new StringBuilder("CREATE ");
        List<String> column = new ArrayList<>();
        boolean isUnique = false;
        for (Statistics statistic : statistics) {
            if (statistic.getNonUnique() == 0) {
                isUnique = true;
            }
            column.add(String.format("\"%s\"", statistic.getColumnName()));
        }
        if (isUnique) {
            sql.append("UNIQUE ");
        }
        sql.append("INDEX %s ON public.%s( %s );");
        return String.format(sql.toString(), statistics.get(0).getIndexName(), statistics.get(0).getTableName(), StringUtils.join(column, ","));
    }
}
